﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.UI.WebControls;
using System.Data;

/// <summary>
/// Ar FillDDL klases palīdzību tiek aizpildīti visi nepieciešamie DropDowņi!!!
/// </summary>
public class FillDDL
{
    static SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);
    
    //Lietotāja kompānijas un klienti
    public static void Select_company(DropDownList ddlCompany, string user_id, string client)
    {
        ddlCompany.Items.Clear();
        SqlCommand selCmd = new SqlCommand("SELECT * FROM user_company WHERE id_user=@user_id AND (ISNULL(@client, '') = '' OR ISNULL(@client, '') = client)", conn);
        selCmd.Parameters.Add(new SqlParameter("@user_id", SqlDbType.Int)).Value = user_id;
        selCmd.Parameters.Add(new SqlParameter("@client", SqlDbType.NVarChar)).Value = client;
        conn.Open();
        SqlDataReader dr = selCmd.ExecuteReader();
        while (dr.Read())
        {
            ListItem item = new ListItem();
            item.Value = Convert.ToString(dr[1]);
            item.Text = _Company.SelectCompany(dr[1].ToString(),"Active")._company_name;
            ddlCompany.Items.Add(item);
        }
        ddlCompany.Items.Insert(0, "--Select--");
        dr.Close();
        conn.Close();
    }

    //Adreses
    public static void Select_addresses(DropDownList ddlAddress, string company_id)
    {
        ddlAddress.Items.Clear();
        if (company_id != "--Select--")
        {
            SqlCommand selCmd = new SqlCommand("SELECT * FROM company_address WHERE id_company=@company_id AND statuss=@status", conn);
            selCmd.Parameters.Add(new SqlParameter("@company_id", SqlDbType.Int)).Value = company_id;
            selCmd.Parameters.Add(new SqlParameter("@status", SqlDbType.NVarChar, 50)).Value = "Active";
            conn.Open();
            SqlDataReader dr = selCmd.ExecuteReader();
            while (dr.Read())
            {
                ListItem item = new ListItem();
                item.Value = Convert.ToString(dr[0]);
                item.Text = dr[2].ToString();
                ddlAddress.Items.Add(item);
            }
            ddlAddress.Items.Insert(0, "--Select--");
            dr.Close();
            conn.Close();
        }
        else
        {
            ddlAddress.Items.Insert(0, "--Select--");
        }
    }

    //Bankas
    public static void Select_banks(DropDownList ddlBanks, string company_id)
    {
        ddlBanks.Items.Clear();
        if (company_id != "--Select--")
        {
            List<int> id_bank = new List<int>();
            SqlCommand selCmd = new SqlCommand("SELECT id_bank FROM company_bank WHERE id_company=@company_id GROUP BY id_bank", conn);
            selCmd.Parameters.Add(new SqlParameter("@company_id", SqlDbType.Int)).Value = company_id;
            conn.Open();
            SqlDataReader dr = selCmd.ExecuteReader();
            while (dr.Read())
            {
                id_bank.Add(Convert.ToInt32(dr["id_bank"]));
            }
            dr.Close();
            conn.Close();

            foreach (var bank_id in id_bank)
            {
                SqlCommand selCmd1 = new SqlCommand("SELECT id, bank_name FROM bank WHERE id=@bank_id GROUP BY id, bank_name", conn);
                selCmd1.Parameters.Add(new SqlParameter("@bank_id", SqlDbType.Int)).Value = bank_id;
                conn.Open();
                SqlDataReader dr1 = selCmd1.ExecuteReader();
                while (dr1.Read())
                {
                    ListItem item = new ListItem();
                    item.Value = Convert.ToString(dr1[0]);
                    item.Text = dr1[1].ToString();
                    ddlBanks.Items.Add(item);
                }                
                dr1.Close();
                conn.Close();
            }
            ddlBanks.Items.Insert(0, "--Select--");
        }
        else
        {
            ddlBanks.Items.Insert(0, "--Select--");
        }
    }

    //Bankas konti
    public static void Select_bank_accounts(DropDownList ddlBankAccounts, string company_id, string bank_id)
    {
        ddlBankAccounts.Items.Clear();
        if (bank_id != "--Select--")
        {
            List<int> id_company_bank = new List<int>();
            SqlCommand selCmd = new SqlCommand("SELECT id FROM company_bank WHERE id_bank=@bank_id AND id_company=@company_id", conn);
            selCmd.Parameters.Add(new SqlParameter("@bank_id", SqlDbType.Int)).Value = bank_id;
            selCmd.Parameters.Add(new SqlParameter("@company_id", SqlDbType.Int)).Value = company_id;
            conn.Open();
            SqlDataReader dr = selCmd.ExecuteReader();
            while (dr.Read())
            {
                id_company_bank.Add(Convert.ToInt32(dr["id"]));
            }
            dr.Close();
            conn.Close();

            foreach (var com_bank_ID in id_company_bank)
            {
                SqlCommand selCmd1 = new SqlCommand("SELECT id_account_number, account_number FROM account_number WHERE id_company_bank=@company_bank_id", conn);
                selCmd1.Parameters.Add(new SqlParameter("@company_bank_id", SqlDbType.Int)).Value = com_bank_ID;
                conn.Open();
                SqlDataReader dr1 = selCmd1.ExecuteReader();
                while (dr1.Read())
                {
                    ListItem item = new ListItem();
                    item.Value = Convert.ToString(dr1[0]);
                    item.Text = dr1[1].ToString();
                    ddlBankAccounts.Items.Add(item);
                }
                dr1.Close();
                conn.Close();
            }
            ddlBankAccounts.Items.Insert(0, "--Select--");
        }
        else
        {
            ddlBankAccounts.Items.Insert(0, "--Select--");
        }
    }

    //Document Type
    public static void Fill_Document_Type(DropDownList ddlDocumentType)
    {
        conn.Close();
        ddlDocumentType.Items.Clear();
        SqlCommand selCmd = new SqlCommand("SELECT id, type_name FROM document_type", conn);
        conn.Open();
        SqlDataReader dr = selCmd.ExecuteReader();        
        while (dr.Read())
        {
            ListItem item = new ListItem();
            item.Value = dr["id"].ToString();
            item.Text = dr["type_name"].ToString();
            ddlDocumentType.Items.Add(item);
        }
        dr.Close();
        conn.Close();
        ddlDocumentType.Items.Insert(0, "--Select--");

    }

    //Payment Type
    public static void Fill_Payment_Type(DropDownList ddlPaymentType)
    {
        ddlPaymentType.Items.Clear();
        SqlCommand selCmd = new SqlCommand("SELECT id, type_name FROM payment_type", conn);
        conn.Open();
        SqlDataReader dr = selCmd.ExecuteReader();        
        while (dr.Read())
        {
            ListItem item = new ListItem();
            item.Value = dr["id"].ToString();
            item.Text = dr["type_name"].ToString();
            ddlPaymentType.Items.Add(item);
        }
        dr.Close();
        conn.Close();
        ddlPaymentType.Items.Insert(0, "--Select--");
    }

    //Licence Type
    public static void Fill_Licence_Type(DropDownList ddlLicenceType)
    {
        ddlLicenceType.Items.Clear();
        SqlCommand selCmd = new SqlCommand("SELECT id, type_licence FROM licence_type", conn);
        conn.Open();
        SqlDataReader dr = selCmd.ExecuteReader();
        while (dr.Read())
        {
            ListItem item = new ListItem();
            item.Value = dr["id"].ToString();
            item.Text = dr["type_licence"].ToString();
            ddlLicenceType.Items.Add(item);
        }
        dr.Close();
        conn.Close();
        ddlLicenceType.Items.Insert(0, "--Select--");
    }

    //Banks
    public static void Fill_Bank(DropDownList ddlBank)
    {
        ddlBank.Items.Clear();
        SqlCommand selCmd = new SqlCommand("SELECT id, bank_name FROM bank", conn);
        conn.Open();
        SqlDataReader dr = selCmd.ExecuteReader();
        while (dr.Read())
        {
            ListItem item = new ListItem();
            item.Value = dr["id"].ToString();
            item.Text = dr["bank_name"].ToString();
            ddlBank.Items.Add(item);
        }
        ddlBank.Items.Insert(0, "--Select--");
        dr.Close();
        conn.Close();
    } 
}